package com.indicatorcalculation.lk
/*
  topic : IndicatorCalculation
  author : lvkun
  date : 2023.4.11
 */
import org.apache.spark.sql.SparkSession

object IndicatorCalculation02 {

  def main(args: Array[String]): Unit = {

    val spark = SparkSession.builder()
      .appName("IndicatorCalculation02")
      .config("spark.sql.warehouse.dir","hdfs://master:9000/user/hive/warehouse")
      .enableHiveSupport()
      .getOrCreate()

    //TODO 读取Hive中的表格
    val orders = spark.table("dwd.orders_lk")
    val customer = spark.table("dwd.customer_lk")
    val nation = spark.table("dwd.nation_lk")
    val region = spark.table("dwd.region_lk")

    //TODO 建立临时表
    orders.createOrReplaceTempView("orders_temp")
    customer.createOrReplaceTempView("customer_temp")
    nation.createOrReplaceTempView("nation_temp")
    region.createOrReplaceTempView("region_temp")

    //TODO 计算下单数量最多的五个国家并打印输出(降序排序)
    val top5Nations = spark.sql(
      """
        |select nation.name count(orders.custkey) as orders_count
        |from orders_temp
        |join customer_temp on orders.custkey = custmoer.key
        |join nation_temp on custmoer.nationkey = nation.nationkey
        |join region_temp on nation.regionkey = region.regionkey
        |group by nation.name
        |order by orders_count desc
        |limit 5
        |""".stripMargin)

    //TODO 展示数据
    top5Nations.show()



  }

}
